package com.nl.deal;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.nl.DealDataToSendMain;
import com.nl.redis.RedisCluster;
import com.nl.util.ConfigUtils;
import com.nl.util.ConnectionManager;
import com.nl.util.StringUtil;



public class SynchDictData implements Runnable {
	
	private static final Logger log = LoggerFactory.getLogger(SynchDictData.class);
	private StringBuilder stationMgrInfoRelationSql = new StringBuilder();
	private StringBuilder smsTemplateSql = new StringBuilder();
	private StringBuilder thresholdSql = new StringBuilder();
	private StringBuilder blackUserSql = new StringBuilder();
	private StringBuilder msgContentsSql = new StringBuilder();
	//同步数据库中所有项目发送量
	private StringBuilder allMgrThresholdSql = new StringBuilder();
	
	public static int upPerLimit = Integer.valueOf(ConfigUtils.getInstance().getValue("upPerLimit"));
	public static int lowerPerLimit = Integer.valueOf(ConfigUtils.getInstance().getValue("lowerPerLimit"));
	public static int thresholdControl = Integer.valueOf(ConfigUtils.getInstance().getValue("thresholdControl"));
	private int otherSynchCount = 0;
	private String rediscfg;
	private RedisCluster cluster;
	private boolean first = true;
	public SynchDictData(String rediscfg) {
		this.rediscfg = rediscfg;
	}
	
	@Override
	public void run() {
		init();
		while(true) {
			try{
				synchThreshold();
				synchDict();
				synchOther();
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				Thread.sleep(5*1000);
				otherSynchCount ++;
				if (otherSynchCount == 30) {
					synchDict();
					synchOther();
					otherSynchCount = 0;
				}
			} catch (InterruptedException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	
	private void init() {
		
		blackUserSql.append("select PHONE_NUMBER from XUYI_BLACK_USERS");
		thresholdSql.append(" select a.id, ") 
		.append("	   a.is_threshold, ") 
		.append("   a.threshold, ") 
		.append("   a.start_date, ") 
		.append("   a.CYCLE_THRESHOLD,")
		.append("   a.CYCLE,")
		.append("   a.end_date, ") 
		.append("   b.total_count, ") 
		.append("   b.year_count, ") 
		.append("   b.quarter_count, ") 
		.append("   b.month_count, ") 
		.append("   b.week_count, ") 
		.append("   b.day_count, ") 
		.append("   b.modify_time, ") 
		.append("   b.status, ") 
		.append("  to_char(b.flag_time, 'yyyymmdd') as today ") 
		.append(" from esms_mgr_template_info a, sms_realtime_control b ") 
		.append("  where a.id = b.id ") 
		.append(" and a. template_state= 2 ")  
		.append("  and a.mgr_state = 0 ")
		.append("  and a.OPEN_ACCOUNT = 1 ");
	  
		smsTemplateSql.append("select t.id, ")
		.append("  t.template_state, ")
		.append("  t.start_date,    ")   
		.append("  t.is_local, ")
		.append("   t.local_date ,")
		.append("   t.flag ,")
		.append("  to_char(decode(t.end_date, null, trunc(sysdate + 1), t.end_date),'yyyy-mm-dd hh24:mi:ss') end_date ")
		.append("  from esms_mgr_template_info t ")
		.append("  where t.template_state = 2 and t.mgr_state = 0 and t.OPEN_ACCOUNT = 1");
		
		stationMgrInfoRelationSql.append("select ")
		.append("a.station_id, ")
		.append("b.city_ext_id, ")
		.append("a.region_id, ")
		.append("a.region_name, ")
		.append("a.station_name, ")
		.append("b.id, ")
		.append("b.name ")
		.append("from sms_base_station a, esms_mgr_template_info b ")
		.append("where a.id = b.id ")
		.append("and b.mgr_state = 0 and b.template_state = 2 and b.OPEN_ACCOUNT = 1");
		
		msgContentsSql.append("select ")
		.append(" t.content,")
		.append(" t.id from esms_mgr_template_info t  where t.template_state = 2 and t.mgr_state = 0 and t.OPEN_ACCOUNT = 1");
		
		allMgrThresholdSql.append("  select t.id,t.time_count,t.day_count,t.week_count,t.month_count,t.quarter_count,t.year_count,t.total_count from sms_realtime_control t");
	}
	
	private void synchDict() throws SQLException {
		//黑名单用户
		Map<String, String> blackUserTmp = new HashMap<String, String>();
		
		//项目短信内容，key项目id
		Map<String, String> msgContensTmp = new HashMap<String, String>();
		
		 Connection conn = null;
	     ResultSet rs = null;
	     PreparedStatement pres = null;
	     conn = ConnectionManager.getInstance().getConnection();
         pres = conn.prepareStatement(blackUserSql.toString());
         rs = pres.executeQuery();
         while(rs.next()) {
        	 try{
        		 String msisdn = rs.getString("PHONE_NUMBER");
            	 blackUserTmp.put(msisdn,"XYGD");
        	 } catch (Exception e) {
        		 e.printStackTrace();
        	 }
         }
         
         if (!blackUserTmp.isEmpty()) {
        	 DataCache.blackUser = blackUserTmp;
        	 
        	 log.info("blackUser synch successful,blackUser size "+DataCache.blackUser.size());
         }else{
        	 DataCache.blackUser.clear();
         }
         
         
         
         pres = conn.prepareStatement(msgContentsSql.toString());
         rs = pres.executeQuery();
         while(rs.next()) {
        	String id = rs.getString("id");
        	try{
        		String content = rs.getString("content");
        		msgContensTmp.put(id, content);
        	} catch (Exception e) {
        		log.error("project "+id+" msgContens synch failed!",e);
        	}
         }
         
         
         if (!msgContensTmp.isEmpty()) {
        	 DataCache.msgContens = msgContensTmp;
        	 log.info("msgContens synch successful,msgContens size "+DataCache.msgContens.size());
         }else{
        	 DataCache.msgContens.clear();
         }
         
         if (null != rs) {
        	 rs.close();
         }
         
         if (null != pres) {
        	 pres.close();
         }
         
         if (null != conn) {
        	 conn.close();
         }
		DealDataToSendMain.dictInitSuccessful = true;
		log.info("all dict data synch successful! status = "+DealDataToSendMain.dictInitSuccessful);
	}
	
	
	private void synchThreshold() throws SQLException, IOException {
		//只在第一次同步时加载已发送量
		if (first) {
			cluster = new RedisCluster(rediscfg);
		}
		//阀值控制表
		Map<String, ThresholdInfo> thresholdMapTmp = new HashMap<String, ThresholdInfo>();
		Map<String, String> mgrs=new HashMap<String,String>();
		int count=0;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		Connection conn = null;
	   ResultSet rs = null;
	     PreparedStatement pres = null;
	     conn = ConnectionManager.getInstance().getConnection();
	     
	     
	   //同步数据库中的所有发送量数据
		    if (null != cluster) {
		    pres = conn.prepareStatement(allMgrThresholdSql.toString());
		    rs = pres.executeQuery();
		    while(rs.next()) {
		    	try {
		    		String mgrId = rs.getString("id");
			       	String dayCount=rs.getString("day_count");
			       	String weekCount=rs.getString("week_count");
			       	String monthCount=rs.getString("month_count");
			       	String quarterCount=rs.getString("quarter_count");
			       	String yearCount=rs.getString("year_count");
			       	String totalCount=rs.getString("total_count");
			       	
			       		if(!mgrs.containsKey(mgrId)){
	                		mgrs.put(mgrId, "1");
	                		cluster.hmset("mgrs", mgrs);
	                		count++;
	                	}
			       		int dbTotalCount=Integer.valueOf(totalCount);
			       		String redisTotalString = cluster.get(mgrId+"totalCount");
			       		//若redis中没有记录项目阀值，则给0.
			       		if(StringUtil.nullOrBlank(redisTotalString)){
			       			cluster.set(mgrId+"dayCount", "0");
		    				 cluster.set(mgrId+"weekCount", "0");
		    				 cluster.set(mgrId+"monthCount","0");
		    				 cluster.set(mgrId+"quarterCount","0");
		    				 cluster.set(mgrId+"yearCount","0");
		    				 cluster.set(mgrId+"totalCount","0");
			       		}
			       		
			       		int redisTotalCount=Integer.valueOf(cluster.get(mgrId+"totalCount"));
			       					       					       		
			       		cluster.set(mgrId+"dayCount",dayCount);
			       		if(dbTotalCount>redisTotalCount){			     
			            	cluster.set(mgrId+"weekCount", weekCount);
			            	cluster.set(mgrId+"monthCount",monthCount);
			            	cluster.set(mgrId+"quarterCount",quarterCount);
			            	cluster.set(mgrId+"yearCount",yearCount);
			            	cluster.set(mgrId+"totalCount",totalCount);
			            	log.info(mgrId+":dayCount"+dayCount+";weekCount:"+weekCount+";monthCount:"+monthCount+";quarterCount:"+quarterCount+";yearCount:"+yearCount+";totalCount"+totalCount);
			       		}
		    	} catch (Exception e) {
		    		log.error("redis同步数据中的数据时发生异常:"+e.toString());
		    		e.printStackTrace();
		    	}
		    }
		    log.info("first sync success ,total:"+count);
		    }
	     
		    
	    	 pres = conn.prepareStatement(thresholdSql.toString());
	    	 //pres.setString(1, sdf.format(new Date()));
	    	 rs = pres.executeQuery();
	    	 while(rs.next()) {
	           	 String id = rs.getString("ID");
	           	 try{
	           		 ThresholdInfo tInfo = new ThresholdInfo();
	                    tInfo.setMgrId(id);
	                    tInfo.setIsThreshold(rs.getInt("IS_THRESHOLD"));
	                    long totalThreshold = rs.getLong("THRESHOLD");
	                    long cycleThreshold = rs.getLong("CYCLE_THRESHOLD");
	                    
	                    //upPerLimit=100  lowerPerLimit=100 thresholdControl=500000
	                    
	                    if (totalThreshold > thresholdControl) {
	                    	totalThreshold = totalThreshold * upPerLimit / 100;
	                    } else {
	                    	totalThreshold = totalThreshold * lowerPerLimit / 100;
	                    }
	                    
	                    if (cycleThreshold > thresholdControl) {
	                    	cycleThreshold = cycleThreshold * upPerLimit / 100;
	                    } else {
	                    	cycleThreshold = cycleThreshold * lowerPerLimit / 100;
	                    }
	                    tInfo.setTotalThreshold(totalThreshold);
	                    tInfo.setCycle(rs.getInt("CYCLE"));
	                    tInfo.setCycleThreshold(cycleThreshold);
//	                    tInfo.setThresholdStartTime(rs.getTimestamp("THRESHOLD_START_TIME"));
//	                    tInfo.setThresholdEndTime(rs.getTimestamp("THRESHOLD_END_TIME"));
	                    long dayCount = 0;
	                    if (rs.getInt("STATUS") == 1) {
	                        tInfo.setTotalCount(0);
	                        tInfo.setYearCount(0);
	                        tInfo.setQuarterCount(0);
	                        tInfo.setMonthCount(0);
	                        tInfo.setWeekCount(0);
	                        tInfo.setDayCount(0);
//	                        tInfo.setTimeCount(0);
	                        tInfo.setStatus(2);//已重置
	                        
	                      //重置阀值
	                        
	                         cluster = new RedisCluster(rediscfg);
	                    	 cluster.set(id+"dayCount", "0");
		    				 cluster.set(id+"weekCount", "0");
		    				 cluster.set(id+"monthCount","0");
		    				 cluster.set(id+"quarterCount","0");
		    				 cluster.set(id+"yearCount","0");
		    				 cluster.set(id+"totalCount","0");
		    				 
	         
	                    } else {
	                    	dayCount = rs.getLong("DAY_COUNT");
	                        tInfo.setTotalCount(rs.getLong("TOTAL_COUNT"));
	                        tInfo.setYearCount(rs.getLong("YEAR_COUNT"));
	                        tInfo.setQuarterCount(rs.getLong("QUARTER_COUNT"));
	                        tInfo.setMonthCount(rs.getLong("MONTH_COUNT"));
	                        tInfo.setWeekCount(rs.getLong("WEEK_COUNT"));
	                        tInfo.setDayCount(dayCount);
	                        tInfo.setStatus(rs.getInt("STATUS"));
	                    }
	                    tInfo.setModyfyTime(rs.getString("MODIFY_TIME"));
	                    tInfo.setToday(rs.getString("TODAY"));
	                    thresholdMapTmp.put(id, tInfo);
	                    if (null != cluster) {
	                    	cluster.set(id, dayCount+"");
	    				}
	           	 } catch (Exception e) {
	           		 log.error("project "+id+" threshold synch failed!",e);
	           	 } finally{
	           		 if(null!= cluster){
	           			cluster.close();
	           			cluster = null;
	           		 }
	           	 }
	           	 
	            }
	    	 
	     
	     
       	 
        if (null != rs) {
       	 rs.close();
        }
        
        if (null != pres) {
       	 pres.close();
        }
        
        if (null != conn) {
       	 conn.close();
        }
        
        if (null != cluster) {
        	cluster.close();
        	cluster = null;
        }
        if (!thresholdMapTmp.isEmpty()) {
        	DealDataToSendMain.thresholdUpdateSuccess = true;
		   	 DataCache.thresholdMap = thresholdMapTmp;
		   	 log.info("Threshold synch successful,threshold size "+DataCache.thresholdMap.size());
        }else{
        	DataCache.thresholdMap.clear();
        }
        first = false;
	}
	
	private void synchOther() throws SQLException {
			
			//短信模板表,key项目id
			Map<String, SmsTemplateAttr> smsTemplateAttrMapTmp = new HashMap<String, SmsTemplateAttr>();
			
			//短信信息和基站关联表，key项目id
			Map<String, StationMgrRelation> stationMgrINfoRelationMapTmp = new HashMap<String, StationMgrRelation>();
			
			//基站和项目对应关系，key基站id
			Map<String, String> stationMgrRelationMapTmp = new HashMap<String, String>();
					
			Connection conn = null;
			ResultSet rs = null;
		    PreparedStatement pres = null;
		    conn = ConnectionManager.getInstance().getConnection();
	        pres = conn.prepareStatement(smsTemplateSql.toString());
	        rs = pres.executeQuery();
	        while(rs.next()) {
	       	String id = rs.getString("id");
	       	try{
	       		String state = rs.getString("template_state");
	       		String start_date = rs.getString("start_date");
//	           	String real_time = rs.getString("real_time");
	           	String is_local = rs.getString("is_local");
	           	String end_date = rs.getString("end_date");
//	           	String start_time = rs.getString("start_time");
//	           	String end_time = rs.getString("end_time");
	           	String local_date = rs.getString("local_date");
	           	String mgrFlag = rs.getString("flag");
	           	SmsTemplateAttr smsTemplateAttr = new SmsTemplateAttr();
	           	smsTemplateAttr.setId(id);
	           	smsTemplateAttr.setState(state);
	           	smsTemplateAttr.setStart_date(start_date);
//	           	smsTemplateAttr.setReal_time(real_time);
	           	smsTemplateAttr.setIs_local(is_local);
	           	smsTemplateAttr.setEnd_date(end_date);
//	           	smsTemplateAttr.setStart_time(start_time);
//	           	smsTemplateAttr.setEnd_time(end_time);
	           	smsTemplateAttr.setLocal_date(local_date);
	           	smsTemplateAttr.setFlag(mgrFlag);
	           	smsTemplateAttrMapTmp.put(id, smsTemplateAttr);
	       	} catch (Exception e) {
	       		log.error("project "+id+" smsTemplateAttr synch failed!",e);
	       	}
	        }
	        
	        if (!smsTemplateAttrMapTmp.isEmpty()) {
	       	 DataCache.smsTemplateAttrMap = smsTemplateAttrMapTmp;
	       	 log.info("smsTemplateAttr synch successful,smsTemplateAttr size "+DataCache.smsTemplateAttrMap.size());
	        }else{
	        	DataCache.smsTemplateAttrMap.clear();
	        }
	        
	        
	        pres = conn.prepareStatement(stationMgrInfoRelationSql.toString());
	        rs = pres.executeQuery();
	        while(rs.next()) {
	       	 String station_id = rs.getString("station_id");
	       	 try {
	       		 String city_id = rs.getString("city_ext_id");
	           	 String region_id = null==rs.getString("region_id") ?  "" :  rs.getString("region_id");
	           	 String region_name = null==rs.getString("region_name") ?  "" :  rs.getString("region_name");
	           	String station_name = null==rs.getString("station_name") ?  "" :  rs.getString("station_name");
	           	String id = rs.getString("id");
	           	String name = rs.getString("name");
	           	StationMgrRelation stationMgrRelation = new StationMgrRelation();
	           	stationMgrRelation.setCity_id(city_id);
	           	stationMgrRelation.setId(id);
	           	stationMgrRelation.setName(name);
	           	stationMgrRelation.setRegion_id(region_id);
	           	stationMgrRelation.setRegion_name(region_name);
	           	stationMgrRelation.setStation_name(station_name);
	           	stationMgrINfoRelationMapTmp.put(id, stationMgrRelation);
	           	if (stationMgrRelationMapTmp.containsKey(station_id)) {
	           		stationMgrRelationMapTmp.put(station_id, stationMgrRelationMapTmp.get(station_id)+","+id);
	           	} else {
	           		stationMgrRelationMapTmp.put(station_id, id);
	           	}
	           	
	       	 } catch (Exception e) {
	       		 log.error("station "+station_id+" station info synch failed!",e);
	       	 }
	        }
	        
	        
	        if (!stationMgrINfoRelationMapTmp.isEmpty()) {
	       	 DataCache.stationMgrINfoRelationMap = stationMgrINfoRelationMapTmp;
	       	 log.info("stationMgrINfoRelation synch successful,stationMgrINfoRelation size "+DataCache.stationMgrINfoRelationMap.size());
	        }else{
	        	DataCache.stationMgrINfoRelationMap.clear();
	        }
	        
	        if (!stationMgrRelationMapTmp.isEmpty()) {
	       	 DataCache.stationMgrRelationMap = stationMgrRelationMapTmp;
	       	 log.info("stationMgrRelation synch successful,stationMgrRelation size "+DataCache.stationMgrRelationMap.size());
	        }else{
	        	DataCache.stationMgrRelationMap.clear();
	        }
	       	 
	        if (null != rs) {
	       	 rs.close();
	        }
	        
	        if (null != pres) {
	       	 pres.close();
	        }
	        
	        if (null != conn) {
	       	 conn.close();
	        }
	}
}
